package com.express.dao.impl;

import com.express.bean.Courier;
import com.express.bean.User;
import com.express.dao.BaseCourierDao;
import com.express.dao.BaseExpressDao;
import com.express.util.DruidUtil;

import java.sql.*;
import java.util.*;

/**
 * @Author: laixiangdong
 * @Date 2020/12/08
 */
public class CourierDaoMysql implements BaseCourierDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(*) courierdatasize, COUNT( TO_DAYS( createtime )= TO_DAYS( NOW()) OR NULL ) courierdataday FROM courier";
    private static final String SQL_FIND_ALL = "SELECT a.id id, a.username username, a.userphone userphone, a.password password, a.cardid cardid, a.createtime createtime, a.logintime logintime,( SELECT COUNT( b.id ) FROM express b WHERE b.sysphone = a.userphone ) expressnumber FROM courier a LIMIT ?,?";
    private static final String SQL_FIND_ALL_2 = "SELECT a.id id, a.username username, a.userphone userphone, a.password password, a.cardid cardid, a.createtime createtime, a.logintime logintime,( SELECT COUNT( b.id ) FROM express b WHERE b.sysphone = a.userphone ) expressnumber FROM courier a";
    private static final String SQL_FIND_BY_PHONE = "SELECT a.id id, a.username username, a.userphone userphone, a.password password, a.cardid cardid, a.createtime createtime, a.logintime logintime,( SELECT COUNT( b.id ) FROM express b WHERE b.sysphone = a.userphone ) expressnumber FROM courier a WHERE a.userphone = ?";
    private static final String SQL_INSERT = "INSERT INTO courier(username, userphone, cardid, password, createtime) VALUES (?, ?, ?, ?, NOW())";
    private static final String SQL_UPDATE = "UPDATE courier SET username = ?, userphone = ?, cardid = ?, password = ? WHERE id = ?";
    private static final String SQL_DELETE = "DELETE FROM courier WHERE id=?";

    @Override
    public Map<String, Integer> console() {
        Map<String, Integer> data = new HashMap<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            result = state.executeQuery();
            if (result.next()) {
                int CourierDataSize = result.getInt("CourierDataSize");
                int CourierDataDay = result.getInt("CourierDataDay");
                data.put("courierDataSize", CourierDataSize);
                data.put("courierDataDay", CourierDataDay);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        List<Courier> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_ALL);
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            } else {
                state = conn.prepareStatement(SQL_FIND_ALL_2);
            }
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String password = result.getString("password");
                Timestamp loginTime = result.getTimestamp("logintime");
                Timestamp createTime = result.getTimestamp("createtime");
                int expressNumber = result.getInt("expressnumber");
                String cardId = result.getString("cardid");
                Courier courier = new Courier(id, userName, password, userPhone, loginTime, createTime, expressNumber, cardId);
                data.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    @Override
    public Courier findByPhone(String phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            state.setString(1, phone);
            result = state.executeQuery();
            if (result.next()) {
                int id = result.getInt("id");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String password = result.getString("password");
                String cardId = result.getString("cardid");
                Timestamp loginTime = result.getTimestamp("logintime");
                Timestamp createTime = result.getTimestamp("createtime");
                int expressNumber = result.getInt("expressnumber");
                return new Courier(id, userName, password, userPhone, loginTime, createTime, expressNumber, cardId);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    @Override
    public Boolean insert(Courier courier) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1, courier.getUserName());
            state.setString(2, courier.getUserPhone());
            state.setString(3, courier.getCardId());
            state.setString(4, courier.getPassword());
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return false;
    }

    @Override
    public Boolean update(int id, Courier newCourier) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1, newCourier.getUserName());
            state.setString(2, newCourier.getUserPhone());
            state.setString(3, newCourier.getCardId());
            state.setString(4, newCourier.getPassword());
            state.setInt(5, id);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return false;
    }

    @Override
    public Boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1, id);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }
}
